diff options
Diffstat (limited to 'app/[lng]/admin')
| -rw-r--r-- | app/[lng]/admin/temp-db-viewer/actions.ts | 65 | ||||
| -rw-r--r-- | app/[lng]/admin/temp-db-viewer/page.tsx | 141 |
2 files changed, 206 insertions, 0 deletions
diff --git a/app/[lng]/admin/temp-db-viewer/actions.ts b/app/[lng]/admin/temp-db-viewer/actions.ts new file mode 100644 index 00000000..753c1b28 --- /dev/null +++ b/app/[lng]/admin/temp-db-viewer/actions.ts @@ -0,0 +1,65 @@ +"use server" + +/** + * 사적이고 우회적인 요청사항... + +-- readonly 사용자 생성 (비밀번호 설정) +CREATE USER readonly WITH PASSWORD 'tempReadOnly_123'; + + +-- evcp 데이터베이스에 연결할 수 있는 권한 부여 +GRANT CONNECT ON DATABASE evcp TO readonly; + +-- 조회할 스키마 사용 권한 부여 +GRANT USAGE ON SCHEMA public TO readonly; +GRANT USAGE ON SCHEMA soap TO readonly; +GRANT USAGE ON SCHEMA nonsap TO readonly; +GRANT USAGE ON SCHEMA mdg TO readonly; + +-- 기존 모든 테이블에 대한 SELECT 권한 부여 +GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; +GRANT SELECT ON ALL TABLES IN SCHEMA soap TO readonly; +GRANT SELECT ON ALL TABLES IN SCHEMA nonsap TO readonly; +GRANT SELECT ON ALL TABLES IN SCHEMA mdg TO readonly; + + */ + +import { Pool } from "pg" + +export interface QueryResultState { + columns: string[] + // eslint-disable-next-line @typescript-eslint/no-explicit-any + rows: Record<string, any>[] + error?: string +} + +export async function executeSqlAction( + prevState: QueryResultState, + formData: FormData +): Promise<QueryResultState> { + const query = (formData.get("query") as string | null) ?? "" + + if (!query.trim()) { + return { ...prevState, error: "쿼리를 입력해주세요." } + } + + try { + const connectionString = process.env.READONLY_DB_URL + + if (!connectionString) { + return { ...prevState, error: "READONLY_DB_URL 환경변수가 설정되지 않았습니다." } + } + + const pool = new Pool({ connectionString }) + const result = await pool.query(query) + await pool.end() + + return { + columns: result.fields.map((f) => f.name), + // eslint-disable-next-line @typescript-eslint/no-explicit-any + rows: result.rows as Record<string, any>[], + } + } catch (err) { + return { ...prevState, error: (err as Error).message } + } +}
\ No newline at end of file diff --git a/app/[lng]/admin/temp-db-viewer/page.tsx b/app/[lng]/admin/temp-db-viewer/page.tsx new file mode 100644 index 00000000..6692e63e --- /dev/null +++ b/app/[lng]/admin/temp-db-viewer/page.tsx @@ -0,0 +1,141 @@ +"use client"; + +import * as React from "react"; +import { useActionState, useState } from "react"; +import { executeSqlAction, type QueryResultState } from "./actions"; +import { Textarea } from "@/components/ui/textarea"; +import { Button } from "@/components/ui/button"; +import { toast } from "sonner"; + +// CSV 변환 유틸 +function convertToCSV(columns: string[], rows: Record<string, any>[]): string { + const escape = (value: any) => { + if (value === null || value === undefined) return ""; + const str = String(value).replace(/"/g, '""'); + return `"${str}"`; + }; + + const header = columns.map(escape).join(","); + const lines = rows.map((row) => + columns.map((col) => escape(row[col])).join(",") + ); + return [header, ...lines].join("\r\n"); +} +// ──────────────────────────────────────────────────────────────────────────────── +// Main page component +// ──────────────────────────────────────────────────────────────────────────────── + +export default function SqlEditorPage() { + const [query, setQuery] = useState<string>(""); + + const initialState: QueryResultState = { + columns: [], + rows: [], + }; + + // useActionState: 서버 액션과 클라이언트 상태 연결 + const [state, formAction, isPending] = useActionState< + QueryResultState, + FormData + >(executeSqlAction, initialState); + + // CSV 내보내기 핸들러 + const handleExportCSV = React.useCallback(() => { + if (state.rows.length === 0) { + toast.info("내보낼 결과가 없습니다."); + return; + } + + const csv = convertToCSV(state.columns, state.rows); + const blob = new Blob([csv], { type: "text/csv;charset=euc-kr;" }); + const url = URL.createObjectURL(blob); + const link = document.createElement("a"); + link.href = url; + link.download = "query_result.csv"; + link.click(); + URL.revokeObjectURL(url); + }, [state.columns, state.rows]); + + // 오류 toast 표시 + React.useEffect(() => { + if (state.error) { + toast.error(state.error); + } + }, [state.error]); + + return ( + <div className="w-full p-4 flex flex-col h-[100dvh] gap-4"> + {/* 상단: 쿼리 입력 영역 */} + <form + action={formAction} + className="flex flex-col min-h-0 space-y-4 overflow-auto" + > + <Textarea + name="query" + className="flex font-mono text-sm" + value={query} + onChange={(e) => setQuery(e.target.value)} + placeholder="조회가능스키마: public, mdg, nonsap, soap(로그스키마)" + disabled={isPending} + /> + <div className="flex justify-end gap-2"> + <p className="text-sm text-muted-foreground">조회된 행 수: {state.rows.length}</p> + <div className="flex gap-2"> + <Button type="submit" disabled={isPending}> + {isPending ? "실행 중..." : "실행"} + </Button> + <Button + type="button" + variant="outline" + onClick={handleExportCSV} + disabled={state.rows.length === 0} + > + CSV 내보내기 + </Button> + </div> + </div> + </form> + + {/* 하단: 결과 테이블 영역 */} + <div className="flex-1 overflow-auto p-4 border rounded-md"> + {state.rows.length === 0 ? ( + <p className="text-sm text-muted-foreground"> + {isPending + ? "쿼리 실행 중" + : "결과 여기 표시됨"} + </p> + ) : ( + <div className="w-full overflow-auto"> + <table className="w-full border-collapse text-sm"> + <thead> + <tr> + {state.columns.map((col) => ( + <th + key={col} + className="border bg-muted px-2 py-1 text-left font-medium" + > + {col} + </th> + ))} + </tr> + </thead> + <tbody> + {state.rows.map((row, rowIdx) => ( + <tr key={rowIdx} className="odd:bg-muted/30"> + {state.columns.map((col) => ( + <td key={col} className="border px-2 py-1"> + {row[col] === null || row[col] === undefined + ? "NULL" + : String(row[col])} + </td> + ))} + </tr> + ))} + </tbody> + </table> + </div> + )} + </div> + </div> + ); +} |